package com.ruoyi.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.system.domain.ProjectPurchase;
import com.ruoyi.system.domain.ProjectPurchaseMaterial;
import com.ruoyi.system.domain.vo.ProjectPurchaseMaterialNumVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 项目采购Mapper接口
 *
 * @author ruoyi
 * @date 2025-03-11
 */
public interface ProjectPurchaseMaterialMapper extends BaseMapper<ProjectPurchaseMaterial> {

    @Update("update project_purchase_material set status = ${status} where id = ${id}")
    void setStatus(@Param("id") Integer id, @Param("status") Integer status);

    @Select("<script>select a.*,b.created_time from project_purchase_material as a LEFT JOIN project_purchase_order as b on (a.project_purchase_order_id = b.id) where a.project_purchase_order_id is not null and DATE_FORMAT(b.created_time,'%Y-%m') = #{month} <if test = 'projectId != null' >  AND b.project_id = ${projectId} </if >  order by b.created_time</script>")
    List<ProjectPurchaseMaterial> getListByMonth(@Param("month") String month, @Param("projectId") Integer projectId);

    @Select("<script>SELECT " +
            " sum( price ) AS totalPrice, " +
            " sum( number ) AS totalNum, " +
            " sum( CASE WHEN DATE_FORMAT( a.created_time, '%Y-%m' ) &lt;  #{month} THEN price ELSE 0 END ) AS lastTotalPrice, " +
            " sum( CASE WHEN DATE_FORMAT( a.created_time, '%Y-%m' ) &lt;  #{month} THEN number ELSE 0 END ) AS lastTotalNum, " +
            " sum( CASE WHEN DATE_FORMAT( a.created_time, '%Y-%m' ) =  #{month} THEN price ELSE 0 END ) AS monthPrice, " +
            " sum( CASE WHEN DATE_FORMAT( a.created_time, '%Y-%m' ) =  #{month} THEN number ELSE 0 END ) AS monthNum  " +
            "FROM " +
            " ( " +
            " SELECT " +
            "  a.*, " +
            "  b.created_time  " +
            " FROM " +
            "  project_purchase_material AS a " +
            "  LEFT JOIN project_purchase_order AS b ON ( a.project_purchase_order_id = b.id )  " +
            " WHERE " +
            "  a.project_purchase_order_id IS NOT NULL  " +
            "  <if test = 'projectId != null' >  AND b.project_id = ${projectId} </if >  " +
            " ) a </script>")
    ProjectPurchaseMaterialNumVO getTotalByMonth(@Param("month") String month, @Param("projectId") Integer projectId);
}
